package spider;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtil {
	
	/**
	 * [得到标题宽度]
	 * 
	 * @param sheet
	 * @param length
	 * @param width
	 */
	public static void getTitleWidth(WritableSheet sheet,int length,String[] width) {
		for (int i =0; i < length; i++) {
			sheet.setColumnView(i, Integer.parseInt(width[i]));//序号后面的三列
		}
	}
	
	public static void setExportExcel(HttpServletResponse response,String exprotName,boolean isAddTime) throws Exception{
		response.reset();//重置
		response.setContentType("application/vnd.ms-excel");//对应文件类型
		Date date = new Date(new java.util.Date().getTime());
		String time = (new SimpleDateFormat("HHmmss")).format(date);
		if(isAddTime){
			exprotName = exprotName+time;
		}
		//导出文件名称
		response.setHeader("Content-Disposition", "attachment; filename=\""
				+ toUtf8String(exprotName)+ ".xls\"");
	}
	
	 /**
	   * @author lw
	   * 转换编码Utf8
	   * @param s String
	   * @return String
	   */
		public static String toUtf8String(String s) {
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < s.length(); i++) {
				char c = s.charAt(i);
				if (c >= 0 && c <= 255) {
					sb.append(c);
				} else {
					byte[] b;
					try {
						b = Character.toString(c).getBytes("utf-8");
					} catch (Exception ex) {
						System.out.println(ex);
						b = new byte[0];
					}
					for (int j = 0; j < b.length; j++) {
						int k = b[j];
						if (k < 0) {
							k += 256;
						}
						sb.append("%" + Integer.toHexString(k).toUpperCase());
					}
				}
			}
			return sb.toString();
		}
	
	/**
	 * [导出Excel]
	 * @param os
	 * @param list
	 * @param title
	 * @param labelTitle
	 * @param width
	 * @throws Exception
	 * @throws WriteException
	 */
    public static void createExcel(File file,List<Object[]> list,String[] title,String labelTitle,String[] width
    		,boolean isZj) throws Exception, WriteException{
		if(isZj){
			createExcel2(file, list, title, labelTitle, width);
		}else{
			FileOutputStream os = new FileOutputStream(file);
			WritableWorkbook wwbook = Workbook.createWorkbook(os); //create sheet
			int times = 1;
			int intRows = 600000;
			int intLstSize = list.size();
			if(intLstSize==0){
				times=1;
			}else if(intLstSize%intRows ==0){
				times = intLstSize/intRows;
			}else if(intLstSize%intRows!=0){
				times = intLstSize/intRows +1;
			}
			int[] tempSize = new int[times];
			int intTempTotalRows = intLstSize;
			for(int i=0;i<times;i++){
				if(intTempTotalRows/intRows >=1){
					tempSize[i] = intRows;
				}else{
					tempSize[i] = intTempTotalRows;
				}
				intTempTotalRows = intTempTotalRows - intRows;
			}
			
			
			for(int k=0;k<times;k++){
				WritableSheet sheet = wwbook.createSheet(labelTitle+(k+1), k); //sheet Name
				
				WritableFont times10ptBold = new WritableFont(WritableFont.createFont("宋体"), 20, WritableFont.BOLD,false,UnderlineStyle.SINGLE);
				WritableCellFormat times10BoldFormat = new WritableCellFormat(times10ptBold);
				times10BoldFormat.setAlignment(Alignment.CENTRE);
				
				WritableFont times11ptBold = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
				WritableCellFormat times11BoldFormat = new WritableCellFormat(times11ptBold);
				times11BoldFormat.setAlignment(Alignment.LEFT);
				
				WritableCellFormat threeBorders1 = new WritableCellFormat();
				threeBorders1.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders1.setAlignment(Alignment.CENTRE);
				threeBorders1.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
				
				WritableCellFormat threeBorders2 = new WritableCellFormat();
				threeBorders2.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders2.setAlignment(Alignment.CENTRE);
				
				WritableCellFormat threeBorders3 = new WritableCellFormat();
				threeBorders3.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders3.setAlignment(Alignment.LEFT);
				
				WritableCellFormat threeBorders4 = new WritableCellFormat();
				threeBorders4.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders4.setAlignment(Alignment.RIGHT);
				
				WritableCellFormat threeBorders5 = new WritableCellFormat(NumberFormats.FLOAT);
				threeBorders5.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders5.setAlignment(Alignment.RIGHT);
				
				WritableCellFormat threeBorders6 = new WritableCellFormat(NumberFormats.INTEGER);
				threeBorders6.setBorder(Border.ALL, BorderLineStyle.THIN);
				threeBorders6.setAlignment(Alignment.RIGHT);
				
				getTitleWidth(sheet,title.length,width);//列宽
				
				//设置表的标题
				sheet.mergeCells(0, 0, title.length-1, 0);
				Label labelTitle00 = new Label(0, 0, labelTitle,times10BoldFormat);
				sheet.addCell(labelTitle00);
				SimpleDateFormat format= new java.text.SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
				Date nowDate=new Date();
				sheet.mergeCells(0, 1, title.length-1, 1);
				sheet.setRowView(1,700,false); 
				Label labelTitle01 = new Label(0, 1,format.format(nowDate),times11BoldFormat);
				sheet.addCell(labelTitle01);
				
				//定义单元格样式
				WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false, 
						UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
				//单元格定义
				WritableCellFormat wcf = new WritableCellFormat(wf); 
				// 设置对齐方式
				wcf.setAlignment(jxl.format.Alignment.CENTRE); 
				
				//定义单元格样式
				WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false, 
						UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
				//单元格定义
				WritableCellFormat wcf2 = new WritableCellFormat(wf2); 
				// 设置对齐方式
				wcf2.setAlignment(jxl.format.Alignment.LEFT); 
				
				//设置表的表头
				for (int i = 0; i < title.length; i++) {
					labelTitle00 = new Label(i, 2, title[i], threeBorders1);
					sheet.addCell(labelTitle00);
				}
				int rowNum =3;
				//表体
				for (int i = k*intRows; i < k*intRows+tempSize[k]; i++) {
					int column = 0;
					Object[] columns = list.get(i);
					if(columns !=null && columns.length >0){
						for(int j=0;j<columns.length;j++){
							if(columns[j] instanceof Double || columns[j] instanceof Float){
								Double value=(Double)columns[j];
								labelTitle00 = new Label(column++, rowNum,columns[j].toString(),threeBorders5);
							}else{
								labelTitle00 = new Label(column++, rowNum,columns[j]==null?"":columns[j].toString(),threeBorders3);
							}
							sheet.addCell(labelTitle00);
						}
					}
					rowNum++;
				}
			}
			
			try{
				//写入Exel工作表
				wwbook.write();
				//关闭Excel工作薄对象
				wwbook.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				os.flush();
				os.close();
			}
		}
	    
	}
    
    /**
	 * [导出Excel]
	 * @param os
	 * @param list
	 * @param title
	 * @param labelTitle
	 * @param width
	 * @throws Exception
	 * @throws WriteException
	 */
    public static void createExcel2(File file,List<Object[]> list,String[] title,String labelTitle,String[] width) throws Exception, WriteException{
    	//追加
//    	System.out.println("追加");
    	 Workbook rwb = Workbook.getWorkbook(file);
         WritableWorkbook wwb = Workbook.createWorkbook(file, rwb);
         WritableSheet sheet = wwb.getSheet(0);
         Label labelTitle00 = null;
         int rowNum = sheet.getRows();
//         System.out.println(rowNum);
         
         WritableCellFormat threeBorders5 = new WritableCellFormat(NumberFormats.FLOAT);
		    threeBorders5.setBorder(Border.ALL, BorderLineStyle.THIN);
		    threeBorders5.setAlignment(Alignment.RIGHT);

	    WritableCellFormat threeBorders3 = new WritableCellFormat();
	    	threeBorders3.setBorder(Border.ALL, BorderLineStyle.THIN);
	    	threeBorders3.setAlignment(Alignment.LEFT);
         
         for (int i = 0;i<list.size();i++) {
		    	int column = 0;
		    	Object[] columns = list.get(i);
		    	if(columns !=null && columns.length >0){
		    		for(int j=0;j<columns.length;j++){
		    			if(columns[j] instanceof Double || columns[j] instanceof Float){
//		    				Double value=(Double)columns[j];
		    				labelTitle00 = new Label(column++, rowNum,columns[j].toString(),threeBorders5);
		    			}else{
		    				labelTitle00 = new Label(column++, rowNum,columns[j]==null?"":columns[j].toString(),threeBorders3);
		    			}
		    	    	sheet.addCell(labelTitle00);
		    		}
		    	}
		    	rowNum++;
		    }
         wwb.write();
         wwb.close();
         rwb.close();
	}
    
    public static boolean isBigDecimal(String val)
    {
    	boolean flag=false;
    	try {
    		BigDecimal b=new BigDecimal(val);
    		flag=true;
		} catch (Exception e) {
			flag=false;
		}
		return flag;
    }
    
    public static void sort(int[] b){
	  	 int temp;
	     for(int i=b.length-1;i>0;i--){
	    	 for(int j=0;j<i;j++){
			    if(b[j]>b[j+1]){
				     temp=b[j];
				     b[j]=b[j+1];
				     b[j+1]=temp;
			    }
			}
	     }
	  }

  
}
